<?php

namespace App\DB;

use DB;
use App\Exceptions\ReqException;

class MonitorDB {
	/**
	 * 保存设备监测数据
	 *
	 * @param unknown $devices_no        	
	 * @param unknown $param1        	
	 * @param unknown $param2        	
	 * @param unknown $param3        	
	 * @param unknown $param4        	
	 * @param unknown $param5        	
	 * @param unknown $param6        	
	 * @param unknown $param7        	
	 * @param unknown $param8        	
	 * @param unknown $param9        	
	 * @param unknown $param10        	
	 * @param unknown $longitude        	
	 * @param unknown $latitude        	
	 */
	public static function addMonitorData($uid, $monitorData) {
		// 保存上传数值
		$datetime = date ( "Y-m-d H:i:s" );
		$sql = 'insert into devices_monitor(device_no, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, longitude, latitude, upload_at) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
		$params = [ 
				$monitorData->devices_no,
				$monitorData->param1,
				$monitorData->param2,
				$monitorData->param3,
				$monitorData->param4,
				$monitorData->param5,
				$monitorData->param6,
				$monitorData->param7,
				$monitorData->param8,
				$monitorData->param9,
				$monitorData->param10,
				$monitorData->longitude,
				$monitorData->latitude,
				$datetime 
		];
		DB::insert ( $sql, $params );
		
		// 判断上传数据是否超过临界值
		$match = self::getMatchParams ();
		$device = DeviceDB::getDeviceInfo ( $uid, $monitorData->devices_no );
		$danger_data = [ ];
		foreach ( $match as $item ) {
			// 设备表中设置了临界值，并且上送数据中有对应参数，才会去比对
			if (is_numeric ( $device->{$item->value_name} ) && is_numeric ( $monitorData->{$item->param_name} )) {
				$sign = $item->sign;
				
				if ($sign == '1') {
					// 上传数值大于临界值
					if (floatval ( $monitorData->{$item->param_name} ) >= floatval ( $device->{$item->value_name} )) {
						$danger_data [] = [ 
								$item->value_name => floatval ( $device->{$item->value_name} ),
								'value' => floatval ( $monitorData->{$item->param_name} ) 
						];
					}
				} else if ($sign = '2') {
					// 上传数值小于临界值
					if (floatval ( $monitorData->{$item->param_name} ) <= floatval ( $device->{$item->value_name} )) {
						$danger_data [] = [ 
								$item->value_name => floatval ( $device->{$item->value_name} ),
								'value' => floatval ( $monitorData->{$item->param_name} ) 
						];
					}
				} else {
					// 上传数值不等于临界值
					if (abs ( (floatval ( $monitorData->{$item->param_name} ) - floatval ( $device->{$item->value_name} )) ) < 0.000001) {
						$danger_data [] = [ 
								$item->value_name => floatval ( $device->{$item->value_name} ),
								'value' => floatval ( $monitorData->{$item->param_name} ) 
						];
					}
				}
			}
		}
		
		// 保存超标数据
		if (count ( $danger_data ) > 0) {
			$sql3 = 'insert into devices_danger(device_no, critical_value1, value1, critical_value2, value2, critical_value3, value3, critical_value4, value4, critical_value5, value5, critical_value6, value6, upload_at, marked, notified) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
			$params3 = [ 
					$monitorData->devices_no,
					$device->critical_value1,
					self::getDangerValue ( $danger_data, 'critical_value1' ),
					$device->critical_value2,
					self::getDangerValue ( $danger_data, 'critical_value2' ),
					$device->critical_value3,
					self::getDangerValue ( $danger_data, 'critical_value3' ),
					$device->critical_value4,
					self::getDangerValue ( $danger_data, 'critical_value4' ),
					$device->critical_value5,
					self::getDangerValue ( $danger_data, 'critical_value5' ),
					$device->critical_value6,
					self::getDangerValue ( $danger_data, 'critical_value6' ),
					$datetime,
					'0',
					'0' 
			];
			DB::insert ( $sql3, $params3 );
		}
	}
	
	/**
	 * 分页获取监测数据
	 *
	 * @param unknown $devices_no        	
	 * @param unknown $start_date        	
	 * @param unknown $end_date        	
	 * @param unknown $page        	
	 * @param unknown $size        	
	 */
	public static function getMonitorData($device_no, $start_date, $end_date, $page, $size) {
		$sql = 'select device_no, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, longitude, latitude, upload_at from devices_monitor where device_no = ? and upload_at between ? and ? order by upload_at desc limit ?, ?';
		$num_page = intval ( $page );
		$num_size = intval ( $size );
		$params = [ 
				$device_no,
				$start_date,
				$end_date,
				($num_page - 1) * $num_size,
				$num_size 
		];
		return DB::select ( $sql, $params );
	}
	public static function getMonitorDataCount($device_no, $start_date, $end_date) {
		$sql = 'select count(*) as `count` from devices_monitor where device_no = ? and upload_at between ? and ?';
		$params = [ 
				$device_no,
				$start_date,
				$end_date 
		];
		$result = DB::select ( $sql, $params );
		return $result [0]->count;
	}
	
	/**
	 * 获取某设备最近上传的记录
	 *
	 * @param unknown $device_no        	
	 * @param unknown $count        	
	 */
	public static function getLatestMonitorData($device_no, $page, $size) {
		$sql = 'select device_no, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, longitude, latitude, upload_at from devices_monitor where device_no = ? order by upload_at desc limit ?, ?';
		$num_page = intval ( $page );
		$num_size = intval ( $size );
		$params = [ 
				$device_no,
				($num_page - 1) * $num_size,
				$num_size 
		];
		$monitorData = DB::select ( $sql, $params );
		return $monitorData;
	}
	public static function getLatestMonitorDataCount($device_no) {
		$sql = 'select count(*) as `count` from devices_monitor where device_no = ?';
		$params = [ 
				$device_no 
		];
		$result = DB::select ( $sql, $params );
		return $result [0]->count;
	}
	
	/**
	 * 获取匹配参数
	 */
	public static function getMatchParams() {
		$sql = 'select * from match_params';
		$result = DB::select ( $sql );
		return $result;
	}
	private static function getDangerValue($arr, $value_name) {
		foreach ( $arr as $item ) {
			foreach ( $item as $key => $value ) {
				if ($key == $value_name) {
					return $item ['value'];
				}
			}
		}
		return null;
	}
	/**
	 * 更新预警数据是否被查看标记
	 *
	 * @param unknown $id        	
	 */
	public static function marked($id) {
		$sql = 'update devices_danger set marked = ? where `id` = ?';
		$params = [ 
				'1',
				$id 
		];
		return DB::update ( $sql, $params );
	}
	/**
	 * 获取预警数据信息
	 *
	 * @param unknown $device_no        	
	 * @param unknown $start_date        	
	 * @param unknown $end_date        	
	 * @param unknown $page        	
	 * @param unknown $size        	
	 */
	public static function getDangerData($device_no, $start_date, $end_date, $page, $size, $marked) {
		$sql = 'select `id`, device_no, critical_value1, value1, critical_value2, value2, critical_value3, value3, critical_value4, value4, critical_value5, value5, critical_value6, value6, upload_at, marked from devices_danger where device_no = ? and upload_at between ? and ? ';
		$num_page = intval ( $page );
		$num_size = intval ( $size );
		$params = [ 
				$device_no,
				$start_date,
				$end_date 
		];
		if ($marked == '0') {
			$sql .= 'and marked = ? ';
			$params [] = '0';
		}
		$sql .= 'order by upload_at desc limit ?, ? ';
		$params [] = ($num_page - 1) * $num_size;
		$params [] = $num_size;
		return DB::select ( $sql, $params );
	}
	public static function getDangerDataCount($device_no, $start_date, $end_date, $marked) {
		$sql = 'select count(*) as `count` from devices_danger where device_no = ? and upload_at between ? and ? ';
		$params = [ 
				$device_no,
				$start_date,
				$end_date 
		];
		if ($marked == '0') {
			$sql .= 'and marked = ? ';
			$params [] = '0';
		}
		$result = DB::select ( $sql, $params );
		return $result [0]->count;
	}
	
	/**
	 * 获取某个用户下设备的所有预警数据
	 *
	 * @param unknown $uid        	
	 * @param unknown $start_date        	
	 * @param unknown $end_date        	
	 * @param unknown $page        	
	 * @param unknown $size        	
	 */
	public static function getUserDangerData($uid, $start_date, $end_date, $page, $size) {
		$sql = 'select a.`id`, a.device_no, a.critical_value1, a.value1, a.critical_value2, a.value2, a.critical_value3, a.value3, a.critical_value4, a.value4, a.critical_value5, a.value5, a.critical_value6, a.value6, a.upload_at, a.marked from devices_danger a, devices b ';
		$condition = 'where a.device_no = b.device_no and a.upload_at between ? and ? and a.marked = ? ';
		$num_page = intval ( $page );
		$num_size = intval ( $size );
		$params = [ 
				$start_date,
				$end_date,
				'0' 
		];
		if (! array_contain_value ( PermissionDB::getUserRoles ( $uid ), '02' )) {
			$condition .= 'and b.uid = ? ';
			$params [] = $uid;
		}
		$condition .= 'order by upload_at desc limit ?, ? ';
		$params [] = ($num_page - 1) * $num_size;
		$params [] = $num_size;
		return DB::select ( $sql . $condition, $params );
	}
	public static function getUserDangerDataCount($uid, $start_date, $end_date) {
		$sql = 'select count(*) as `count` from devices_danger a, devices b where a.device_no = b.device_no and a.upload_at between ? and ? and a.marked = ? ';
		$params = [ 
				$start_date,
				$end_date,
				'0' 
		];
		if (! array_contain_value ( PermissionDB::getUserRoles ( $uid ), '02' )) {
			$sql .= 'and b.uid = ? ';
			$params [] = $uid;
		}
		$result = DB::select ( $sql, $params );
		return $result [0]->count;
	}
	
	/**
	 * 获取未推送的预警数据的记录条数
	 */
	public static function getNotifyDataCount($uid) {
		$sql = 'select count(*) as `count` from devices_danger a, devices b where a.device_no = b.device_no and a.marked = ? and a.notified = ? and b.uid = ?';
		$params = [ 
				'0',
				'0',
				$uid 
		];
		$result = DB::select ( $sql, $params );
		
		$sql2 = 'update devices_danger a, devices b set a.notified = ? where a.device_no = b.device_no and a.notified = ? and b.uid = ?';
		$params2 = [ 
				'1',
				'0',
				$uid 
		];
		DB::update ( $sql2, $params2 );
		
		return $result [0]->count;
	}
}